package model;
import java.io.*;
import java.util.*;
import java.awt.image.BufferedImage;
import java.sql.Date;
import java.sql.*;

import javax.imageio.ImageIO;
import javax.naming.NamingException;

import org.apache.log4j.Logger;

import utils.Database;
import utils.DirectoryResolver;
import utils.JNDIFactory;
import utils.Picture;
import utils.ThumbNailer;

// picid integer,
// picname character varying(128),
// piclocation character varying(512),
// picposition character varying(512),
// picsavedate date,
// picsavetime time with time zone,
// picsavepath character varying(1024)

public class PicDatabaseModel {
	
	private static Logger jlog = Logger.getLogger(PicDatabaseModel.class);
	
	private Database dbPicture;
	private String strDatabaseName = new String("jdbc/waiDB");
	private Connection conPicDatabase = null;
	private Statement stmtPicDatabase = null;
	private ResultSet rsltPicDatabase = null;
	private JNDIFactory jndiFactory = JNDIFactory.getInstance();
	
	private Picture picArray[] = new Picture[1000];
	private Picture picCurrent = null;
	
	private File flImage = null;
	private BufferedImage bfdImage = null;
	
	private Vector<Picture> picList = new Vector<Picture>();
	
	private int nPicID = 0;
	private String strPicName;
	private String strPicLocation;
	private String strPicPosition;
	private java.sql.Date dtPicSaveDate;
	private java.sql.Time tmPicSaveTime;
	private String strPicSavePath;
	
	private java.sql.Date dtFrom;
	private java.sql.Date dtTo;
	private java.sql.Time tmFrom;
	private java.sql.Time tmTo;
	
	private java.sql.Date picDate = null;
	
	
	public PicDatabaseModel( ) {
	}
	
	public PicDatabaseModel(String dbName ) {
		strDatabaseName = new String( dbName );
	}
	
	private void OpenConnection() {
		jlog.debug("OpenConnection");
		try {
			conPicDatabase = jndiFactory.getConnection(strDatabaseName);
			stmtPicDatabase = conPicDatabase.createStatement();
			
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	private void CloseConnection() {
		jlog.debug("CloseConnection");
		try {
			stmtPicDatabase.close();
			conPicDatabase.close();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	private void ExecuteCommand(String sqlcmd, boolean update) {
		jlog.debug("ExecuteCommand: " + sqlcmd + " " + update);
		try {
			if( update )
				stmtPicDatabase.executeUpdate(sqlcmd);
			else
				rsltPicDatabase = stmtPicDatabase.executeQuery(sqlcmd);
			
		} catch (SQLException e) {
			jlog.debug(e.getMessage());
			e.printStackTrace();
		}
	}
	
	private String savePictureToHDD() {
		String retval = null;
	    
	    try {
	    	flImage = new File(DirectoryResolver.getAbsoluteBaseDirectoryPath() + strPicSavePath + strPicName);
	    	flImage.mkdirs();
	    	
			ImageIO.write(bfdImage, "jpg", flImage);
			retval = flImage.getPath();
			
		} catch (IOException e) {
			jlog.debug(e.toString());
			e.printStackTrace();
		}
	    
	    return retval;
	}
	
	private void insertPictureToDatabase() {
		OpenConnection();
		
		ExecuteCommand("SELECT MAX(picid)+1 AS cnt FROM picturetable;", false);
		
		try {
			rsltPicDatabase.next();
			nPicID = rsltPicDatabase.getInt("cnt");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		ExecuteCommand("INSERT INTO picturetable (picid, picname, piclocation, picposition, picsavedate, picsavetime, picsavepath)" + " "
				+ "VALUES ('" + nPicID + "','" + strPicName + "','" + strPicLocation + "','" + strPicPosition + "','" + dtPicSaveDate.clone() + "','" + tmPicSaveTime + "','" + strPicSavePath + "');",
				true);
		
		CloseConnection();
	}
	
	private void DeletePictureFromHDD() {
		File file = new File( strPicSavePath + strPicName );
        if(file.exists()){
            file.delete();
        }
	}
	
	private void DeletePictureFromDatabase() {
		OpenConnection();
		
		ExecuteCommand("DELTE FROM picturetable WHERE " +
				"piclocation ='" + strPicLocation + "' AND " + "picposition = '" + strPicPosition + "' AND " + "picname = '" + strPicName + "';",
				true);
		
		CloseConnection();
	}
	
	private void FindPictureInDatabase() {
		OpenConnection();
		
		picList.clear();
		
		ExecuteCommand("SELECT * FROM picturetable" + " " +
				"WHERE( (picsavedate BETWEEN '"+ this.dtFrom.clone() +"' AND '" + this.dtTo.clone() + "')" + " " +
				"AND (picsavetime BETWEEN '" + this.tmFrom + "' AND '" + this.tmTo + "') );",
				false);
		
		try {
			while( rsltPicDatabase.next() ) {
				if( rsltPicDatabase.getRow() == 0 )
					break;
				
				java.util.Date utilDate = new Date( java.util.Date.UTC ( rsltPicDatabase.getDate("picsavedate").getYear(), 
																			rsltPicDatabase.getDate("picsavedate").getMonth(),
																			rsltPicDatabase.getDate("picsavedate").getDate(),
																			rsltPicDatabase.getTime("picsavetime").getHours(),
																			rsltPicDatabase.getTime("picsavetime").getMinutes(),
																			rsltPicDatabase.getTime("picsavetime").getSeconds() ));
				
				picList.add(new Picture( rsltPicDatabase.getInt("picid"),
																	rsltPicDatabase.getString("picname"),
																	rsltPicDatabase.getString("piclocation"),
																	rsltPicDatabase.getString("picposition"),
																	rsltPicDatabase.getString("picsavepath"),
																	utilDate ));
				
//				picArray[rsltPicDatabase.getRow() - 1] = new Picture( rsltPicDatabase.getInt("picid"),
//																	rsltPicDatabase.getString("picname"),
//																	rsltPicDatabase.getString("piclocation"),
//																	rsltPicDatabase.getString("picposition"),
//																	rsltPicDatabase.getString("picsavepath"),
//																	utilDate );
			}
			
		} catch (SQLException e) {
			jlog.debug(e.getMessage());
			e.printStackTrace();
		}
		
		CloseConnection();
	}
	
	public boolean InsertNewDatabaseEntry() {
		
		nPicID = 0;
		strPicName = new String("BildName");
		strPicLocation = new String("Location");
		strPicPosition = new String("Position");
		picDate = new java.sql.Date(114, 11, 24);//new Date(2014, 12, 23, 23, 59, 59);
		java.sql.Time time = new java.sql.Time(picDate.getTime());
		
		//picDate.setHours(11);
		//picDate.setMinutes(59);
		//picDate.setSeconds(59);
		
		strPicSavePath = new String("SavePath");
		
		try {
			OpenConnection();
			
			ExecuteCommand("SELECT max(picid)+1 AS cnt FROM picturetable", false);
			
			if(rsltPicDatabase.next())
				nPicID = rsltPicDatabase.getInt("cnt");
			
			
			jlog.debug(rsltPicDatabase.toString());
			ExecuteCommand("INSERT INTO picturetable (picid, picname, piclocation, picposition, picsavedate, picsavetime, picsavepath)" + " "
					+ "VALUES ('" + nPicID + "','" + strPicName + "','" + strPicLocation + "','" + strPicPosition + "','" + picDate.clone() + "','" + time + "','" + strPicSavePath + "')", true);
			
			CloseConnection();
			
		} catch (SQLException e) {
			jlog.debug(e.getMessage());
			jlog.debug(rsltPicDatabase.toString());
			e.printStackTrace();
		}
		
		
		return true;
	}
	
	public void InsertPicture(String strPicLocation, 
			String strPicPosition,
			java.util.Date date,
			BufferedImage image) {
		
		this.strPicLocation = new String(strPicLocation);
		this.strPicPosition = new String(strPicPosition);
		this.dtPicSaveDate	= new java.sql.Date(date.getTime());
		this.tmPicSaveTime 	= new java.sql.Time(date.getTime());
		this.bfdImage 		= new BufferedImage(image.getWidth(), image.getHeight(), image.getType());
		this.bfdImage.setData(image.getData());
		
		int Tag = dtPicSaveDate.getDate();
		int Monat = dtPicSaveDate.getMonth();
		int Jahr = dtPicSaveDate.getYear();
		int Stunde = tmPicSaveTime.getHours();
		int Minute = tmPicSaveTime.getMinutes();
		int Sekunde = tmPicSaveTime.getSeconds();
		
		strPicName = new String( Stunde + "-" + Minute + "-" + Sekunde + ".jpg" );
		
		strPicSavePath = new String( "PictureDatabase/" + 
		    strPicLocation + "/" + 
			strPicPosition + "/" + 
		    (Jahr+1900) + "/" + 
			(Monat+1) + "/" +
		    Tag + "/");
		
		String absoluteSavedPath = savePictureToHDD();

		if(absoluteSavedPath != null && absoluteSavedPath != "") {
			insertPictureToDatabase();
			ThumbNailer.generateThumbNail(strPicSavePath + strPicName);
		}
		
	}
	
	public void DeletePicture( String strPicName, 
			String strPicLocation, 
			String strPicPosition ) {
		
	}
	
	public Vector<Picture> GetPictureFromTo( String strPicLocation,
			String strPicPosition,
			java.util.Date dateFrom,
			java.util.Date dateTo) {
		
		this.strPicLocation = new String(strPicLocation);
		this.strPicPosition = new String(strPicPosition);
		this.dtFrom 		= new java.sql.Date(dateFrom.getTime());
		this.dtTo 			= new java.sql.Date(dateTo.getTime());
		this.tmFrom 		= new java.sql.Time(dateFrom.getTime());
		this.tmTo 			= new java.sql.Time(dateTo.getTime());
		
		FindPictureInDatabase();

		return picList;
	}
	
	public Picture GetPictureByID( int id ) {
		
		OpenConnection();
		ExecuteCommand("SELECT * FROM picturetable WHERE picid= " + "'" + id + "'", false);
		
		try {
			rsltPicDatabase.next();
			this.nPicID = rsltPicDatabase.getInt("picid");
			this.strPicLocation = new String( rsltPicDatabase.getString("piclocation") );
			this.strPicPosition = new String( rsltPicDatabase.getString("picposition"));
			this.strPicSavePath = new String( rsltPicDatabase.getString("picsavepath"));
			this.picDate = new java.sql.Date(114, 11, 24);//new java.sql.Date( rsltPicDatabase.getDate("picsavedate"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		CloseConnection();
		
		picCurrent = new Picture(nPicID, strPicName, strPicLocation, strPicPosition, strPicSavePath, picDate);
		
		return picCurrent;
	}
	
	public int GetMaxPicID() {
		int temp = 0;
		OpenConnection();
		
		ExecuteCommand("SELECT max(picid) AS cnt FROM picturetable", false);
		
		try {
			if(rsltPicDatabase.next())
				temp = rsltPicDatabase.getInt("cnt");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		CloseConnection();
		return temp;
	}
	
	public int GetPictureArrayLength() {
		return picArray.length;
	}

}
